﻿using DocumentFormat.OpenXml.Office2010.Excel;
using Magicodes.ExporterAndImporter.Excel;
using Mapster;
using Microsoft.Extensions.Configuration;
using NP.BPMReportPlatform.Cadre.Dto;
using NP.BPMReportPlatform.Common;
using NP.BPMReportPlatform.Common.Dtos;
using NP.BPMReportPlatform.Contract.Dtos;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.Entities.BPM_Business.Cadre;
using NP.BPMReportPlatform.Entities.BPM_Platform;
using NP.BPMReportPlatform.RecurringJobs;
using NP.Enterprise.Utils.Http;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using RazorEngine.Templating;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Ubiety.Dns.Core.Records.NotUsed;
using Volo.Abp.Application.Dtos;
using static NP.BPMReportPlatform.Cadre.Enum.CadreEnum;
using static NP.BPMReportPlatform.Common.TreeBuildTool;

namespace NP.BPMReportPlatform.Cadre
{
    /// <summary>
    /// 干部转身计划接口
    /// </summary>
    public class CadreService : BPMReportPlatformAppService, ICadreService
    {
        #region Variables

        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly INPRestHttpHelper _nPRestHttp;
        private readonly string _esbSAPUrl;
        private readonly ICommonService _commonService;
        private readonly IExcelImporter _importer;
        private readonly ICadreSyncJob _cadreEmailService;
        private readonly ILogger _logger;

        #endregion

        #region Constructor

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sqlSugarAppService"></param>
        public CadreService(ISqlSugarAppService sqlSugarAppService,
            INPRestHttpHelper nPRestHttp, IConfiguration configuration, ICommonService commonService, IExcelImporter importer, ICadreSyncJob cadreEmailService,
            ILogger logger)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _nPRestHttp = nPRestHttp;
            _esbSAPUrl = configuration["Nippon:ESB:SYQ_ESB_ERPHR_API"];
            _commonService = commonService;
            _importer = importer;
            _cadreEmailService = cadreEmailService;
            _logger = logger;
        }

        #endregion

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<GetCadreResultOutput>> GetCadreResult(GetCadreResultInput input)
        {
            RefAsync<int> totalCount = 0;

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;

            var role = getUserRoles(currentWorkNumber);
            var listQueryable = await _db.Queryable<CadreEntity>()
                .WhereIF(!input.WorkNumber.IsNullOrEmpty(), t => t.WorkNumber.Contains(input.WorkNumber))
                .WhereIF(!input.UserName.IsNullOrEmpty(), t => t.UserName.Contains(input.UserName))
                .WhereIF(!input.TeacherName.IsNullOrEmpty(), t => t.TeacherName.Contains(input.TeacherName))
                .WhereIF(input.StartDate != null, t => input.StartDate <= t.CreateTime)
                .WhereIF(input.EndDate != null, t => input.EndDate >= t.CreateTime)
                .WhereIF(input.Status != null, t => t.Status == input.Status)
                .WhereIF(!role.Item1, t => SqlFunc.ContainsArray(role.Item2, t.WorkNumber) || currentWorkNumber == t.WorkNumber)
                .Where(t => t.Status != -1)
                .OrderBy(t => t.CreateTime, OrderByType.Desc)
                .Select(t => new GetCadreResultOutput()
                {
                }).ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            listQueryable.ForEach(t =>
            {
                t.IsHRBP = role.Item1 || SqlFunc.ContainsArray(role.Item2, t.WorkNumber);
                t.IsMySelf = currentWorkNumber == t.WorkNumber;
            });

            // 返回结果
            return new PagedResultDto<GetCadreResultOutput>()
            {
                TotalCount = totalCount,
                Items = listQueryable
            };
        }

        /// <summary>
        /// 更新干部转身数据
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<string> SubmitCadre(GetCadreResultOutput input)
        {
            var entity = input.Adapt<CadreEntity>();
            if (entity.StartDate != null && entity.Month > 0)
            {
                entity.EndDate = entity.StartDate.Value.AddMonths(entity.Month ?? 0);
            }

            await CreateHistory(entity.Id, input.UpdateType, input.Reason);

            if (string.IsNullOrEmpty(entity.Id))
            {
                var count = await _db.Queryable<CadreEntity>().Where(t => t.Status != -1 && t.Status < CadreStatusEnum.通过.GetHashCode() && t.WorkNumber == input.WorkNumber).CountAsync();
                if (count > 0)
                {
                    throw new Exception($"{input.UserName}已有在途干部转身数据，请勿重复添加!");
                }
                entity.Id = Guid.NewGuid().ToString();
                entity.CreateTime = DateTime.Now;
                entity.Status = CadreStatusEnum.上岗前沟通.GetHashCode();
                entity.StatusName = CadreStatusEnum.上岗前沟通.ToString();
                _db.Insertable(entity).ExecuteCommand();
                return "保存成功";
            }
            else
            {
                if (input.UpdateType == 1)
                {
                    _db.Updateable(entity).ExecuteCommand();
                    return "编辑成功";
                }
                else if (input.UpdateType == 2)
                {
                    _db.Updateable(entity).UpdateColumns(t => new { t.TeacherCode, t.TeacherName }).ExecuteCommand();
                    return "导师修改成功";
                }
                else if (input.UpdateType == -1)
                {
                    entity.Status = -1;
                    entity.StatusName = "已删除";
                    _db.Updateable(entity).UpdateColumns(t => new { t.Status, t.StatusName }).ExecuteCommand();
                    return "删除成功";
                }
                else if (input.UpdateType == 5 || input.UpdateType == 6)
                {
                    _db.Updateable(entity).UpdateColumns(t => new { t.Record1, t.Record2, t.Record3, t.Record4 }).ExecuteCommand();
                    if (input.UpdateType == 5)
                    {
                        entity.Status = CadreStatusEnum.转身计划制定.GetHashCode();
                        entity.StatusName = CadreStatusEnum.转身计划制定.ToString();
                        _db.Updateable(entity).UpdateColumns(t => new { t.Status, t.StatusName }).ExecuteCommand();
                        // 给干部发邮件
                        _cadreEmailService.SendCadreEmail(CadreEmailEnum.转身计划制定提醒, entity.Id);
                        return "上岗前沟通记录提交成功";
                    }
                    return "上岗前沟通记录暂存成功";
                }
                else if (input.UpdateType == 20)
                {
                    entity.Status = CadreStatusEnum.终止.GetHashCode();
                    entity.StatusName = CadreStatusEnum.终止.ToString();
                    entity.CloseReason = input.Reason;
                    entity.CloseTime = DateTime.Now;
                    _db.Updateable(entity).UpdateColumns(t => new { t.Status, t.StatusName, t.CloseReason, t.CloseTime }).ExecuteCommand();
                    return "终止成功";
                }
            }
            return "失败";
        }

        private async Task CreateHistory(string cadreId, int? updateType, string reason)
        {
            try
            {
                var entity = new CadreOperateHistory
                {
                    Id = Guid.NewGuid().ToString(),
                    CadreId = cadreId,
                    Reason = reason,
                    UpdateType = updateType,
                    WorkNumber = CurrentUser?.UserName ?? "",
                    CreateTime = DateTime.Now,
                };
                await _db.Insertable(entity).ExecuteCommandAsync();
            }
            catch (Exception ex)
            {
                _logger.Error($"CreateHistory Error:{ex.Message}");
            }
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<MemoryStream> ExportCadreResultAsync(string id)
        {
            var data = await GetCadreDetail(id);
            var fs = File.OpenRead(Environment.CurrentDirectory + "/wwwroot/excel/干部转身信息表模版.xlsx");
            IWorkbook workbook = new XSSFWorkbook(fs);
            ISheet sheet = workbook.GetSheetAt(0);
            #region // 边框样式

            // 全局样式
            ICellStyle borderStyle = workbook.CreateCellStyle();
            borderStyle.BorderLeft = BorderStyle.Thin;
            borderStyle.BorderRight = BorderStyle.Thin;
            borderStyle.BorderTop = BorderStyle.Thin;
            borderStyle.BorderBottom = BorderStyle.Thin;
            #endregion

            // 基本信息
            sheet.GetRow(1).GetCell(1).SetCellValue(data.Cadre.UserName);
            sheet.GetRow(1).GetCell(4).SetCellValue(data.Cadre.WorkNumber);
            sheet.GetRow(2).GetCell(1).SetCellValue(data.Cadre.OrganizationName);
            sheet.GetRow(3).GetCell(1).SetCellValue(data.Cadre.ApplyerJob);
            sheet.GetRow(3).GetCell(4).SetCellValue(data.Cadre.ApplyerTitle);
            sheet.GetRow(4).GetCell(1).SetCellValue(data.Cadre.TeacherName);
            sheet.GetRow(4).GetCell(4).SetCellValue(data.Cadre.LeaderName);
            sheet.GetRow(5).GetCell(1).SetCellValue(data.Cadre.StartDate.Value.ToString("yyyy年MM月dd日") + "~" + data.Cadre.EndDate.Value.ToString("yyyy年MM月dd日"));
            sheet.GetRow(5).GetCell(4).SetCellValue(data.Cadre.Month ?? 0);

            // 上岗前沟通
            sheet.GetRow(9).GetCell(0).SetCellValue(data.Cadre.Record1);
            sheet.GetRow(14).GetCell(0).SetCellValue(data.Cadre.Record2);
            sheet.GetRow(19).GetCell(0).SetCellValue(data.Cadre.Record3);
            sheet.GetRow(24).GetCell(0).SetCellValue(data.Cadre.Record4);

            // 转身计划
            sheet.GetRow(30).GetCell(0).SetCellValue(data.Cadre.PlanRecord1);
            sheet.GetRow(35).GetCell(0).SetCellValue(data.Cadre.PlanRecord2);

            // 转身计划总结

            int startrow = 41;
            var cadreprocess = data.CaderProcess.Where(t => t.ResultInstanceNumber == data.Cadre.ResultInstanceNumber).ToList();
            var cadreprocess2 = data.CaderProcess.Where(t => t.ResultInstanceNumber == data.Cadre.RestartResultInstanceNumber).ToList();

            if (cadreprocess.Count > 0)
            {
                SetNewRow(startrow, sheet, cadreprocess.Count);
                foreach (var item in cadreprocess)
                {
                    IRow r = sheet.GetRow(startrow++);
                    r.GetCell(0).SetCellValue(item.KeyIndex);
                    r.GetCell(1).SetCellValue(item.Plan);
                    r.GetCell(2).SetCellValue(item.Measure);
                    r.GetCell(3).SetCellValue(item.NeedUser);
                    r.GetCell(4).SetCellValue(item.FinishDate.Value.ToString("yyyy年MM月dd"));
                    r.GetCell(5).SetCellValue(item.Result);
                }
            }

            startrow = startrow + 3;
            if (cadreprocess2.Count > 0)
            {
                SetNewRow(startrow, sheet, cadreprocess2.Count);
                foreach (var item in cadreprocess2)
                {
                    IRow r = sheet.GetRow(startrow++);
                    r.GetCell(0).SetCellValue(item.KeyIndex);
                    r.GetCell(1).SetCellValue(item.Plan);
                    r.GetCell(2).SetCellValue(item.Measure);
                    r.GetCell(3).SetCellValue(item.NeedUser);
                    r.GetCell(4).SetCellValue(item.FinishDate.Value.ToString("yyyy年MM月dd"));
                    r.GetCell(5).SetCellValue(item.Result);
                }
            }
            else
            {
                startrow++;
            }

            // 导师辅导计划
            startrow = startrow + 3;
            if (data.CadreTeacherRecord.Count > 0)
            {
                SetNewRow(startrow, sheet, data.CadreTeacherRecord.Count);
                int index = 0;
                foreach (var item in data.CadreTeacherRecord)
                {
                    IRow r = sheet.GetRow(startrow++);
                    r.GetCell(0).SetCellValue(item.TalkCount ?? 0);
                    r.GetCell(1).SetCellValue(item.TeacherName);
                    r.GetCell(2).SetCellValue(item.TalkDate.Value.ToString("yyyy年MM月dd"));
                    r.GetCell(3).SetCellValue(item.TalkResult);

                    //var meger = new CellRangeAddress(startrow - 1, startrow - 1, 3, 5);
                    //CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
                    //if (index < (data.CadreTeacherRecord.Count() - 1))
                    //{
                    //    sheet.AddMergedRegion(meger);
                    //}
                    index++;
                }
            }

            // 转身述职记录
            startrow = startrow + 3;
            if (data.CadreResult.Count > 0)
            {
                // SetNewRow(startrow, sheet, data.CadreResult.Count);
                foreach (var item in data.CadreResult)
                {
                    IRow r = sheet.GetRow(startrow++);
                    r.GetCell(0).SetCellValue(item.RegularDate.Value.ToString("yyyy年MM月dd"));
                    r.GetCell(1).SetCellValue(item.RegularResult);
                    r.GetCell(2).SetCellValue(item.RegularSuggest);
                    r.GetCell(5).SetCellValue(item.RegularPendingReason);
                    //var meger = new CellRangeAddress(startrow - 1, startrow - 1, 2, 4);
                    //if (index < (data.CadreTeacherRecord.Count() - 1))
                    //{
                    //    sheet.AddMergedRegion(meger);
                    //}
                    //index++;
                }
            }

            MemoryStream memoryStream = new MemoryStream();
            workbook.Write(memoryStream);
            workbook.Close();
            return memoryStream;
        }

        /// <summary>
        /// 保存导师辅导记录
        /// </summary>
        /// <param name="entity">数据</param>
        public async Task<string> SubmitTeacherRecord(CadreTeacherRecordDto input)
        {
            var entity = input.Adapt<CadreTeacherRecordEntity>();
            entity.Id = Guid.NewGuid().ToString();
            entity.CreateTime = DateTime.Now;
            await _db.Insertable(entity).ExecuteCommandAsync();
            // TODO: 邮件通知导师，直接主管
            return "保存成功";
        }

        /// <summary>
        /// 保存述职总结结果
        /// </summary>
        /// <param name="entity">数据</param>
        public async Task<string> SubmitCadreResult(CadreResultDto input)
        {
            var entity = input.Adapt<CadreResultEntity>();
            entity.Id = Guid.NewGuid().ToString();
            entity.CreateTime = DateTime.Now;
            await _db.Insertable(entity).ExecuteCommandAsync();
            var cadre = await _db.Queryable<CadreEntity>().Where(t => t.Id == entity.CadreId).FirstAsync();
            cadre.Status = entity.RegularResult == "通过" ? CadreStatusEnum.通过.GetHashCode() :
                entity.RegularResult == "不通过" ? CadreStatusEnum.不通过.GetHashCode() :
                CadreStatusEnum.待定.GetHashCode();
            cadre.StatusName = entity.RegularResult;
            _db.Updateable(cadre).UpdateColumns(t => new { t.Status, t.StatusName }).ExecuteCommand();
            return "保存成功";
        }

        /// <summary>
        /// 获取干部转身所有信息
        /// </summary>
        /// <param name="cadreId"></param>
        /// <returns></returns>
        public async Task<CadreDetailDto> GetCadreDetail(string cadreId)
        {
            CadreDetailDto result = new CadreDetailDto();
            result.Cadre = await _db.Queryable<CadreEntity>().Where(t => t.Id == cadreId).FirstAsync();
            result.CaderProcess = _db.Queryable<CadreProcessDetailEntity>().Where(t => t.CadreId == cadreId).OrderBy(t => t.KeyIndex).ToList();
            result.CadreTeacherRecord = _db.Queryable<CadreTeacherRecordEntity>().Where(t => t.CadreId == cadreId).OrderBy(t => t.CreateTime).ToList();
            result.CadreResult = _db.Queryable<CadreResultEntity>().Where(t => t.CadreId == cadreId).OrderBy(t => t.CreateTime).ToList();
            return result;
        }

        /// <summary>
        /// 获取新员工
        /// </summary>
        /// <param name="workNumber"></param>
        /// <returns></returns>
        public async Task<object> GetUserDetail(string workNumber)
        {
            return await _dbPlatform.Queryable<Users, UserExtension>((a, b) => a.WorkNumber == b.Sapemployeeid)
                .Where((a, b) => a.Status == 1 && (a.WorkNumber.Contains(workNumber) || a.UserName.Contains(workNumber)))
                .OrderBy((a, b) => a.CreateDate, OrderByType.Desc)
                .Select((a, b) => new
                {
                    a.WorkNumber,
                    a.UserName,
                    a.FullPathText,
                    a.FullPathCode,
                    a.OrganizationName,
                    a.OrganizationId,
                    ApplyJob = b.Employeepostname,
                    ApplyerTitle = b.Posnc,
                    LeaderCode = b.Bossemployeeid,
                    LeaderName = b.Bossname
                }).Take(10).ToListAsync();
        }

        #region 报表
        /// <summary>
        /// 导师辅导计划报表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CadreReportTeacherOutPut>> GetReportTeacher(CadreReportSearchDto input)
        {
            RefAsync<int> totalCount = 0;


            DateTime start = DateTime.Now;
            DateTime end = DateTime.Now;
            if (!input.StartDate.IsNullOrEmpty())
            {
                DateTime.TryParse(input.StartDate, out start);
            }
            if (!input.EndDate.IsNullOrEmpty())
            {
                DateTime.TryParse(input.EndDate, out end);
            }

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;

            var listQueryable = await _db.Queryable<CadreEntity>()
                .WhereIF(!input.StartDate.IsNullOrEmpty(), t => t.StartDate >= start)
                .WhereIF(!input.EndDate.IsNullOrEmpty(), t => t.StartDate <= end)
                .WhereIF(!input.KeyWord.IsNullOrEmpty(), t => t.TeacherName.Contains(input.KeyWord) || t.TeacherCode.Contains(input.KeyWord))
                .Where(t => t.Status != -1)
                .GroupBy(t => t.TeacherCode)
                .Select(t => new CadreReportTeacherOutPut()
                {
                    TeacherCode = t.TeacherCode,
                    TeacherCount = SqlFunc.AggregateCount(t.TeacherCode)
                }).ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            if (listQueryable != null && listQueryable.Count() > 0)
            {
                var worknumbers = listQueryable.Select(t => t.TeacherCode).ToList();
                var users = _dbPlatform.Queryable<Users>().Where(t => worknumbers.Contains(t.WorkNumber)).ToList();
                var userextends = _dbPlatform.Queryable<UserExtension>().Where(t => worknumbers.Contains(t.Sapemployeeid)).ToList();
                listQueryable.ForEach(item =>
                {
                    var user = users.Where(t => t.WorkNumber == item.TeacherCode).FirstOrDefault();
                    item.TeacherName = user?.UserName;
                    item.OrgName = user?.FullPathText;
                    item.Posnc = userextends.Where(t => t.Sapemployeeid == item.TeacherCode).FirstOrDefault()?.Employeepostname;
                });
            }

            // 返回结果
            return new PagedResultDto<CadreReportTeacherOutPut>()
            {
                TotalCount = totalCount,
                Items = listQueryable
            };
        }

        /// <summary>
        /// 转身执行统计表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<List<CadreReportOrgCountOutPut>> GetReportCadreOrgCount(CadreReportSearchDto input)
        {
            RefAsync<int> totalCount = 0;

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;

            // 获取所有组织
            var orgs = _dbPlatform.Queryable<Organizations>().Where(t => t.Status == 1)
                .Select(t => new CadreReportOrgCountOutPut()
                {
                    id = t.DeptCode,
                    OrgName = t.Name,
                    FullPathText = t.FullPathText,
                    ParentOrgCode = t.ParentDeptCode == "99000000" ? string.Empty : t.ParentDeptCode,
                }).ToList();

            DateTime start = DateTime.Now;
            DateTime end = DateTime.Now;
            if (!input.StartDate.IsNullOrEmpty())
            {
                DateTime.TryParse(input.StartDate, out start);
            }
            if (!input.EndDate.IsNullOrEmpty())
            {
                DateTime.TryParse(input.EndDate, out end);
            }

            if (orgs != null && orgs.Count() > 0)
            {
                var cadretotallist = await _db.Queryable<CadreEntity>()
                    .WhereIF(!input.StartDate.IsNullOrEmpty(), t => t.StartDate >= start)
                    .WhereIF(!input.EndDate.IsNullOrEmpty(), t => t.StartDate <= end)
                    .Where(t => t.Status != -1)
                    .ToListAsync();

                var teachertotallist = await _db.Queryable<CadreTeacherRecordEntity>()
                    .GroupBy(t => t.CadreId).Select(t => new
                    {
                        CadreId = t.CadreId,
                        Count = SqlFunc.AggregateCount(t.CadreId)
                    }).ToListAsync();

                var worknumbers = cadretotallist.Select(t => t.WorkNumber).Distinct().ToList();
                var users = _dbPlatform.Queryable<Users>().Where(t => SqlFunc.ContainsArray(worknumbers, t.WorkNumber)).ToList();
                var cadrelist = new List<CadreEntity>();
                var cadreids = new List<string>();
                orgs.ForEach(org =>
                {
                    List<string> orgnames = org.FullPathText.Split("_").ToList();
                    org.OrgName1 = orgnames.Count() > 0 ? orgnames[0] : "";
                    org.OrgName2 = orgnames.Count() > 1 ? orgnames[1] : "";
                    org.OrgName3 = orgnames.Count() > 2 ? orgnames[2] : "";
                    org.OrgName4 = orgnames.Count() > 3 ? orgnames[3] : "";
                    org.OrgName5 = orgnames.Count() > 4 ? orgnames[4] : "";
                    org.OrgName6 = orgnames.Count() > 5 ? orgnames[5] : "";
                    org.OrgName7 = orgnames.Count() > 6 ? orgnames[6] : "";
                    org.OrgName8 = orgnames.Count() > 7 ? orgnames[7] : "";
                    worknumbers = users.Where(t => t.FullPathCode.Contains(org.id)).Select(t => t.WorkNumber).ToList();
                    cadrelist = cadretotallist.Where(t => worknumbers.Contains(t.WorkNumber)).ToList();
                    // 招到符合条件的数据
                    org.CadreCount = cadrelist.Count();
                    org.UsingCount = cadrelist.Where(t => t.Status < 9).Count();
                    org.ApprovedCount = cadrelist.Where(t => t.Status == CadreStatusEnum.通过.GetHashCode()).Count();
                    org.PendingCount = cadrelist.Where(t => t.Status == CadreStatusEnum.待定.GetHashCode()).Count();
                    org.UnApprovedCount = cadrelist.Where(t => t.Status == CadreStatusEnum.不通过.GetHashCode()).Count();
                    org.EndCount = cadrelist.Where(t => t.Status == CadreStatusEnum.终止.GetHashCode()).Count();
                    // 考核通过、待定、不通过人数下辅导次数 >= 3次的人数，反之为0
                    cadreids = cadrelist.Where(t => t.Status == CadreStatusEnum.通过.GetHashCode() || t.Status == CadreStatusEnum.不通过.GetHashCode() || t.Status == CadreStatusEnum.待定.GetHashCode()).Select(t => t.Id).ToList();
                    org.TeacherRecordCount = teachertotallist.Where(t => cadreids.Contains(t.CadreId) && t.Count >= 3).Count();
                    if (org.ApprovedCount + org.PendingCount + org.UnApprovedCount > 0)
                    {
                        org.ApprovePencent = Math.Round((decimal)org.TeacherRecordCount / (org.ApprovedCount + org.PendingCount + org.UnApprovedCount), 2) * 100 + "%";
                    }
                });
            }

            // 返回结果
            orgs = orgs.Where(t => t.CadreCount > 0).WhereIf(!string.IsNullOrEmpty(input.KeyWord), t => t.id.Contains(input.KeyWord) || t.OrgName.Contains(input.KeyWord)).ToList();
            if (input.IsExport || !string.IsNullOrEmpty(input.KeyWord))
            {
                return orgs;
            }
            return new TreeBuild<CadreReportOrgCountOutPut>().Build(orgs, string.Empty); ;
        }

        /// <summary>
        /// 干部上岗前沟通记录表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CadreReportRecord>> GetReportCadreRecord(CadreReportSearchDto input)
        {
            RefAsync<int> totalCount = 0;

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var role = getUserRoles(currentWorkNumber);

            var listQueryable = await _db.Queryable<CadreEntity>()
                .WhereIF(!input.KeyWord.IsNullOrEmpty(), t => t.WorkNumber.Contains(input.KeyWord) || t.UserName.Contains(input.KeyWord))
                .WhereIF(!role.Item1, t => SqlFunc.ContainsArray(role.Item2, t.WorkNumber))
                .Where(t => t.Status != -1)
                .OrderBy(t => t.CreateTime)
                .Select(t => new CadreReportRecord()
                {
                    UserName = t.UserName,
                    WorkNumber = t.WorkNumber,
                    OrgName = t.OrganizationName,
                    StartDate = t.StartDate.Value.ToString("yyyy-MM-dd"),
                    EndDate = t.StartDate.Value.ToString("yyyy-MM-dd"),
                    Record1 = t.Record1,
                    Record2 = t.Record2,
                    Record3 = t.Record3,
                    Record4 = t.Record4
                }).ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            // 返回结果
            return new PagedResultDto<CadreReportRecord>()
            {
                TotalCount = totalCount,
                Items = listQueryable
            };
        }

        /// <summary>
        /// 导师辅导记录表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CadreReportTeacherRecord>> GetReportCadreTeacherRecord(CadreReportSearchDto input)
        {
            RefAsync<int> totalCount = 0;

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var role = getUserRoles(currentWorkNumber);

            var listQueryable = await _db.Queryable<CadreTeacherRecordEntity, CadreEntity>((t, b) => t.CadreId == b.Id)
                .WhereIF(!input.KeyWord.IsNullOrEmpty(), (t, b) => b.WorkNumber.Contains(input.KeyWord) || b.UserName.Contains(input.KeyWord))
                 .WhereIF(!role.Item1, (t, b) => SqlFunc.ContainsArray(role.Item2, b.WorkNumber))
                .Where((t, b) => b.Status != -1)
                .OrderBy((t, b) => t.CreateTime)
                .Select((t, b) => new CadreReportTeacherRecord()
                {
                    UserName = b.UserName,
                    WorkNumber = b.WorkNumber,
                    OrgName = b.OrganizationName,
                    TalkDate = t.TalkDate.Value.ToString("yyyy-MM-dd"),
                    TalkCount = t.TalkCount ?? 0,
                    TeacherName = t.TeacherName,
                    TalkResult = t.TalkResult
                }).ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            // 返回结果
            return new PagedResultDto<CadreReportTeacherRecord>()
            {
                TotalCount = totalCount,
                Items = listQueryable
            };
        }

        /// <summary>
        /// 转身述职记录表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CadreReportResult>> GetReportCadreResult(CadreReportSearchDto input)
        {
            RefAsync<int> totalCount = 0;

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var role = getUserRoles(currentWorkNumber);

            var listQueryable = await _db.Queryable<CadreResultEntity, CadreEntity>((t, b) => t.CadreId == b.Id)
                .WhereIF(!input.KeyWord.IsNullOrEmpty(), (t, b) => b.WorkNumber.Contains(input.KeyWord) || b.UserName.Contains(input.KeyWord))
                .WhereIF(!role.Item1, (t, b) => SqlFunc.ContainsArray(role.Item2, b.WorkNumber))
                .Where((t, b) => b.Status != -1)
                .OrderBy((t, b) => t.CreateTime)
                .Select((t, b) => new CadreReportResult()
                {
                    UserName = b.UserName,
                    WorkNumber = b.WorkNumber,
                    ApplyerJob = b.ApplyerJob,
                    ApplyerTitle = b.ApplyerTitle,
                    LeaderName = b.LeaderName,
                    OrganizationName = b.OrganizationName,
                    RegularDate = t.RegularDate.Value.ToString("yyyy-MM-dd"),
                    RegularPendingReason = t.RegularPendingReason,
                    RegularResult = t.RegularResult,
                    RegularSuggest = t.RegularSuggest,
                    TeacherName = b.TeacherName
                }).ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            // 返回结果
            return new PagedResultDto<CadreReportResult>()
            {
                TotalCount = totalCount,
                Items = listQueryable
            };
        }

        /// <summary>
        /// 转身计划总结记录表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        public async Task<PagedResultDto<CadreReportProcess>> GetReportProcess(CadreReportSearchDto input)
        {
            RefAsync<int> totalCount = 0;

            // 权限过滤
            var currentWorkNumber = CurrentUser != null && CurrentUser.UserName != null ? CurrentUser.UserName : string.Empty;
            var role = getUserRoles(currentWorkNumber);

            var listQueryable = await _db.Queryable<CadreProcessDetailEntity, CadreEntity>((t, b) => t.CadreId == b.Id)
                .WhereIF(!input.KeyWord.IsNullOrEmpty(), (t, b) => b.WorkNumber.Contains(input.KeyWord) || b.UserName.Contains(input.KeyWord))
                .WhereIF(!role.Item1, (t, b) => SqlFunc.ContainsArray(role.Item2, b.WorkNumber))
                .Where((t, b) => b.Status != -1)
                .OrderBy((t, b) => b.CreateTime).OrderBy((t, b) => t.KeyIndex)
                .Select((t, b) => new CadreReportProcess()
                {
                    UserName = b.UserName,
                    WorkNumber = b.WorkNumber,
                    OrgName = b.OrganizationName,
                    PlanInstanceNumber = t.PlanInstanceNumber,
                    ResultInstanceNumber = t.ResultInstanceNumber,
                    KeyIndex = t.KeyIndex,
                    Measure = t.Measure,
                    NeedUser = t.NeedUser,
                    Plan = t.Plan,
                    Result = t.Result,
                    FinishDate = t.FinishDate.Value.ToString("yyyy-MM-dd"),

                }).ToPageListAsync(input.PageIndex, input.PageSize, totalCount);

            // 返回结果
            return new PagedResultDto<CadreReportProcess>()
            {
                TotalCount = totalCount,
                Items = listQueryable
            };
        }
        #endregion

        #region 私有方法
        /// <summary>
        /// 获取用户权限
        /// </summary>
        /// <returns></returns>
        private Tuple<bool, List<string>> getUserRoles(string currentWorkNumber)
        {
            // 权限过滤
            var userCodes = _commonService.GetUserByRoleFilter(currentWorkNumber, RelationshipTypeEnum.HRBP, true);
            userCodes = userCodes != null && userCodes.Count > 0 ? userCodes : new List<string>() { "-1" };
            bool isAdmin = userCodes.Contains("9999999999");
            if (isAdmin)
            {
                return new Tuple<bool, List<string>>(isAdmin, userCodes);
            }

            return new Tuple<bool, List<string>>(isAdmin, userCodes);
        }


        private void SetNewRow(int startrows, ISheet sheet, int count)
        {
            if (count == 1)
            {
                return;
            }
            var rowSource = sheet.GetRow(startrows);
            var rowStyle = rowSource.RowStyle;//获取当前行样式
                                              //插入行
            sheet.ShiftRows(startrows, sheet.LastRowNum, count - 1);
            for (int i = startrows; i < startrows + count - 1; i++)
            {
                var rowInsert = sheet.CreateRow(i);
                if (rowStyle != null)
                    rowInsert.RowStyle = rowStyle;
                rowInsert.Height = rowSource.Height;

                for (int col = 0; col < rowSource.LastCellNum; col++)
                {
                    var cellsource = rowSource.GetCell(col);
                    var cellInsert = rowInsert.CreateCell(col);
                    var cellStyle = cellsource.CellStyle;
                    //设置单元格样式　　　　
                    if (cellStyle != null)
                        cellInsert.CellStyle = cellsource.CellStyle;
                }
            }
        }
        #endregion
    }
}